﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Web;

namespace Codes {
    public class AlbumGroup {
        public static bool add(string Name, string Remark, int Order, int State, string ImageSize) {
            string sql="insert into AlbumGroup([Name],[Remark],[Addtime],[State],[Order],[Cover],[ImageSize]) values(@Name,@Remark,now(),@State,@Order,'',@ImageSize)";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@Name", Name),
                new OleDbParameter("@Remark", Remark),
                new OleDbParameter("@Order", Order),
                new OleDbParameter("@State", State),
                new OleDbParameter("@ImageSize", ImageSize)
            })>0;
        }
        public static bool update(string Name, string Remark, int Order, int State, string ImageSize, int ID) {
            string sql="update AlbumGroup set Name=@Name,Remark=@Remark,[Order]=@Order,[State]=@State,[ImageSize]=@ImageSize where ID=@ID";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@Name", Name),
                new OleDbParameter("@Remark", Remark),
                new OleDbParameter("@Order", Order),
                new OleDbParameter("@State", State),
                new OleDbParameter("@ImageSize", ImageSize),
                new OleDbParameter("@ID", ID)
            })>0;
        }
        public static bool SetCover(string CoverPath, int ID) {
            string sql="update AlbumGroup set [Cover]=@Cover where ID=@ID";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@Cover", CoverPath),
                new OleDbParameter("@ID", ID)
            })>0;
        }
        public static bool changeOrder(int Order, int ID) {
            string sql="update AlbumGroup set [Order]=@Order where ID=@ID";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@Order", Order),
                new OleDbParameter("@ID", ID)
            })>0;
        }
        public static bool delete(string id) {
            string sql="delete from AlbumGroup where [ID]="+id+"";
            Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql);
            sql="delete from Album where [Album].[GroupID]="+id;
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql)>0;
        }

        public static int getListCount() {
            string sql ="select count(1) from AlbumGroup";
            return (int)Codes.AccessHelper.ExecuteScalar(connectionString.Content, sql);
        }
        public static System.Data.DataTable getList(int pageindex, int pagesize) {
            int notTop=((pageindex-1)*pagesize);
            string sql=string.Empty;
            if(notTop==0) {
                sql="select top "+pagesize+" *,(select count(1) from Album where Album.GroupID=AlbumGroup.ID) as PicCount from AlbumGroup order by [Order] asc";
            } else {
                sql ="select top "+pagesize+" *,(select count(1) from Album where Album.GroupID=AlbumGroup.ID) as PicCount from AlbumGroup where id not in(select top "+((pageindex-1)*pagesize)+" id from AlbumGroup) order by [order] asc";
            }
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Content, sql).Tables[0];
        }

        public static System.Data.DataTable getInfo(string id) {
            string sql="select * from AlbumGroup where id="+id;
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Content, sql).Tables[0];
        }
    }

    public class Album {
        public static bool add(string Name, string UrlPath, string Remark, int Order) {
            string sql="insert into Album([Name],[UrlPath],[Remark],[Addtime],[Order]) values(@Name,@UrlPath,@Remark,'"+DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")+"',@Order)";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@Name", Name),
                new OleDbParameter("@UrlPath", UrlPath),
                new OleDbParameter("@Remark", Remark),
                new OleDbParameter("@Order", Order)                
            })>0;
        }
        public static bool update(string Name, string UrlPath, string Remark, int Order, int ID) {
            string sql="update Album set Name=@Name,Remark=@Remark,[Order]=@Order,[UrlPath]=@UrlPath where ID=@ID";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@Name", Name),
                new OleDbParameter("@UrlPath", UrlPath),
                new OleDbParameter("@Remark", Remark),
                new OleDbParameter("@Order", Order),
                new OleDbParameter("@ID", ID)
            })>0;
        }
        public static bool changeOrder(int Order, int ID) {
            string sql="update Album set [Order]=@Order where ID=@ID";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@Order", Order),
                new OleDbParameter("@ID", ID)
            })>0;
        }
        public static bool delete(string id) {
            string sql="delete from Album where [ID]="+id+";";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql)>0;
        }

        public static int getListCount() {
            string sql ="select count(1) from Album";
            return (int)Codes.AccessHelper.ExecuteScalar(connectionString.Content, sql);
        }
        public static System.Data.DataTable getList(int pageindex, int pagesize) {
            int notTop=((pageindex-1)*pagesize);
            string sql=string.Empty;
            if(notTop==0) {
                sql="select top "+pagesize+" * from Album order by [Order] asc";
            } else {
                sql ="select top "+pagesize+" * from Album where id not in(select top "+((pageindex-1)*pagesize)+" id from Album) order by [order] asc";
            }
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Content, sql).Tables[0];
        }

        public static System.Data.DataTable getInfo(string id) {
            string sql="select * from Album where id="+id;
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Content, sql).Tables[0];
        }
    }
}